from openpyxl import Workbook
import pandas as pd
import os
import glob
def cpk_name():
    folder_path = 'd:/data'
    output_file = 'D:/CPK.xlsx'
    final = []
    excel_files =glob.glob(os.path.join(folder_path, '*.xlsx'))
    for excel_file in excel_files:
        with pd.ExcelFile(excel_file) as xlsx:
            sheet=xlsx.sheet_names
            for sht in sheet:
                if sht[:10]=="Data Input":
                    df=pd.read_excel(excel_file,sht)
                    name=df.iloc[4].tolist()
                    nameb=df.iloc[7].tolist()
                    normal=df.iloc[12].tolist()
                    usl=df.iloc[17].tolist()
                    lsl=df.iloc[18].tolist()
                    for i in range(15,len(name)):
                        print(name[i][4:],nameb[i])
                        if type(nameb[i]) is float:
                            f_name=name[i][4:]
                        else:
                            f_name=name[i][4:]+"-"+str(nameb[i])
                        if normal[i]==0:
                            final.append([f_name,normal[i],usl[i],""])
                        else:
                            final.append([f_name,normal[i],usl[i],lsl[i]])
                    break
    #print(final)
    wb = Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws.append(["Name","Normal","USL","LSL"])
    for row in final:
        ws.append(row)
    wb.save(output_file)
cpk_name()